
**** Step 1: Rename data so as to create a dataset  _n compatible with the geonear package
clear 
use "$path\Intermediary Data\data_PDV_autoroutes_supermarches_v3.dta"
merge 1:1 id_pdv using "$path\Intermediary Data\Base_id_pdv_frompricedata.dta"

replace longitude=longitude/100000
replace latitude=latitude/100000

replace longitude=. if long==0
replace latitude=. if lat==0
drop if long==.
drop if lat==.
gen along=abs(long)
gen alat=abs(lat)
replace longitude=longitude*100000 if along<0.01 & alat<0.01
replace latitude=latitude*100000 if along<0.01 & alat<0.01

drop along alat
replace cp="35000" if cp=="35***"
destring cp, replace
gen dep=int(cp/1000)

sort cp dep id_pdv
gen diff=abs(lat[_n-1]-lat[_n+1])

gen i=0
replace i=1 if diff[_n-1]>3 & diff[_n+1]>3
gen h=0
replace h=1 if longitude<=lat[_n+1]+0.5 & longitude>=lat[_n+1]-0.5 & dep==dep[_n+1] & lat!=. & diff<1


gen long2=longitude
gen lat2=latitude
replace longitude=lat2 if h==1 |i==1
replace latitude=long2 if h==1 |i==1

keep id_pdv longitude latitude cp commune
bys cp: egen m_long=mean(long)
bys cp: egen m_lat=mean(lat)
gen diff_long=long-m_long if long!=.

gen d_lat=lat-m_lat if long!=.
sum (diff_long),d



egen idb=group(longitude latitude)
duplicates tag idb, gen(testid)

keep id_pdv longitude latitude cp commune idb

save "$path\Intermediary Data\id_dpv_idb.dta", replace

rename longitude n_longitude
rename latitude n_latitude

rename idb n_idb
duplicates drop n_idb, force
keep n_*
save "$path\Intermediary Data\n_id_pdv_forlocalshocks.dta", replace

**** Step 2: merge of the _n* dataset

clear 
use "$path\Intermediary Data\n_id_pdv_forlocalshocks.dta", replace
rename n_longitude longitude
rename n_latitude latitude
rename n_idb idb2

geonear idb2 latitude longitude using "$path\Intermediary Data\n_id_pdv_forlocalshocks.dta", n(n_idb n_latitude n_longitude) ign near(10)

rename idb2 idb
save "$path\Intermediary Data\NN10_id_pdv_forlocalshocks.dta", replace


use "$path\Intermediary Data\id_dpv_idb.dta", clear
keep id_pdv idb
save "$path\Intermediary Data\id_dpv_idb.dta", replace

*** Step 3: Create main dataset 

use "$path\Intermediary Data\DataReg_Gasoil", clear

rename date_maj_num date_tokeep
merge m:1 date_tokeep using "$path\Intermediary Data\DataReg_ResidMA_Rotterdam_daily.dta"
drop _merge
rename date_tokeep date_maj_num

drop classif_num autoroute delta_lprix_ht eurusd gasoline_rotterdam_euro
drop if j3==5 
egen daten=group(date_maj_num)
xtset id daten

gen num_obs=_n-1

bysort id (daten): gen dlog_prix_ht=log(prix_ht[_n])-log(prix_ht[_n-1])

merge m:m id_pdv using "$path\Intermediary Data\id_dpv_idb.dta"
keep if _m==3
drop _m


save "$path\Intermediary Data\temp.dta", replace
use "$path\Intermediary Data\temp.dta", clear

**** Step 3.1: Save 10 datasets


bys idb daten: egen dlog_prix_htm=mean(dlog_prix_ht)
duplicates drop idb daten, force 
keep idb daten dlog_prix_htm
drop if idb==.
drop if daten==.

save "$path\Intermediary Data\dprix_idb.dta", replace

foreach i of numlist 1/10 {

use "$path\Intermediary Data\dprix_idb.dta"
keep idb daten dlog_prix_htm
rename idb nid`i'
rename dlog_prix_htm dlog_prix_ht_nid`i'
save "$path\Intermediary Data\nid`i'.dta", replace
}

use "$path\Intermediary Data\temp.dta", clear

**** Step 3.2: identify  average variation among the 10 closest stations
merge m:1 idb using "$path\Intermediary Data\NN10_id_pdv_forlocalshocks.dta"
keep if _merge==3
drop _merge

drop km_to* 

merge m:1 nid1 daten using "$path\Intermediary Data\nid1.dta"
drop if _merge==2
drop _merge

merge m:1 nid2 daten using "$path\Intermediary Data\nid2.dta"
drop if _merge==2
drop _merge

merge m:1 nid3 daten using "$path\Intermediary Data\nid3.dta"
drop if _merge==2
drop _merge

merge m:1 nid4 daten using "$path\Intermediary Data\nid4.dta"
drop if _merge==2
drop _merge


merge m:1 nid5 daten using "$path\Intermediary Data\nid5.dta"
drop if _merge==2
drop _merge


merge m:1 nid6 daten using "$path\Intermediary Data\nid6.dta"
drop if _merge==2
drop _merge

merge m:1 nid7 daten using "$path\Intermediary Data\nid7.dta"
drop if _merge==2
drop _merge


merge m:1 nid8 daten using "$path\Intermediary Data\nid8.dta"
drop if _merge==2
drop _merge


merge m:1 nid9 daten using "$path\Intermediary Data\nid9.dta"
drop if _merge==2
drop _merge


merge m:1 nid10 daten using "$path\Intermediary Data\nid10.dta"
drop if _merge==2
drop _merge



*** Average of non-missing values


egen local_shock10=rmean(dlog_prix_ht_nid1 dlog_prix_ht_nid2 dlog_prix_ht_nid3 dlog_prix_ht_nid4 dlog_prix_ht_nid5 ///
dlog_prix_ht_nid6 dlog_prix_ht_nid7 dlog_prix_ht_nid8 dlog_prix_ht_nid9 dlog_prix_ht_nid10)


save "$path\Intermediary Data\localshock_dataforReg.dta", replace


	 
	 
drop  resid_movav_gasoline
drop dlog_prix_ht longitude latitude nid1-nid10 dlog_prix_ht_nid1-dlog_prix_ht_nid10
*drop prix

drop diesel_rotterdam
save "$path\Intermediary Data\localshock_dataforReg_brent.dta", replace

drop if j3==4
drop daten
egen daten=group(date_maj_num)
drop date_maj_num
xtset id daten
capture drop d_dieselr
gen d_dieselr=ln(diesel_rotterdam_euro)-ln(l1.diesel_rotterdam_euro)
replace d_dieselr=resid_movav_diesel

bysort id (daten):  gen dprix1=log(prix_ht[_n])-log(prix_ht[_n-1])
bys daten: egen m_dprix=mean(dprix1)
drop num_obs
gen num_obs=_n-1
save "$path\Intermediary Data\localshock_dataforReg_v2.dta", replace


